package com.dm.util;

import static com.dm.DmActivity.alEventId;
import static com.dm.DmActivity.alSch;
import static com.dm.DmActivity.alSelectedType;
import static com.dm.DmActivity.alType;
import static com.dm.DmActivity.defultType;
import static com.dm.DmActivity.rangeFrom;
import static com.dm.DmActivity.rangeTo;
import static com.dm.DmActivity.util;
import static com.dm.util.Constant.getNowDateString;
import static com.dm.util.Constant.getNowTimeString;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.widget.Toast;

import com.dm.DmActivity;
import com.dm.bean.Schedule;

public class DBUtil {

    static SQLiteDatabase sld;
    static String DB_NAME = "/data/data/com.dm/myDb";

    // ============================所有處理類型數據庫的方法start==============================
    public static void loadType(Context father)// 從類型數據庫中讀取數據
    {
	try {
	    sld = SQLiteDatabase.openDatabase(DB_NAME, null, SQLiteDatabase.OPEN_READWRITE
		    | SQLiteDatabase.CREATE_IF_NECESSARY);
	    String sql = "create table if not exists type(tno integer primary key,tname varchar2(20));";
	    sld.execSQL(sql);
	    Cursor cursor = sld.query("type", null, null, null, null, null, "tno");
	    int count = cursor.getCount();
	    if (count == 0)// 如果是第一次運行程序，自動創建3個缺省類型
	    {
		for (int i = 0; i < defultType.length; i++) {
		    sql = "insert into type values(" + i + ",'" + defultType[i] + "')";
		    sld.execSQL(sql);
		}
		cursor = sld.query("type", null, null, null, null, null, "tno");
		count = cursor.getCount();
	    }
	    alType.clear();
	    while (cursor.moveToNext()) {
		alType.add(cursor.getString(1));
	    }
	    sld.close();
	    cursor.close();
	} catch (Exception e) {
	    Toast.makeText(father, "類型數據庫打開創建錯誤：" + e.toString(), Toast.LENGTH_LONG).show();
	}
    }

    public static boolean insertType(Context father, String newType)// 更新類型數據庫
    {
	Cursor cursor = null;
	boolean duplicate = false;// false代表沒有類型名稱重複，true代表有重複
	try {
	    sld = SQLiteDatabase.openDatabase(DB_NAME, null, SQLiteDatabase.OPEN_READWRITE);
	    cursor = sld.query("type", null, null, null, null, null, "tno");
	    alType.clear();
	    while (cursor.moveToNext())// 存入新日程時，與數據庫中已有的日程進行比較，如果重複，則標誌位設為true
	    {
		if (newType.equals(cursor.getString(1))) {
		    duplicate = true;
		}
		alType.add(cursor.getString(1));
	    }
	    if (!duplicate) {
		alType.add(newType);
		String sql = "delete from type";
		sld.execSQL(sql);
		for (int i = 0; i < alType.size(); i++) {
		    sql = "insert into type values(" + i + ",'" + alType.get(i) + "')";
		    sld.execSQL(sql);
		}
		Toast.makeText(father, "成功添加類型「" + newType + "」。", Toast.LENGTH_SHORT).show();
	    } else {
		Toast.makeText(father, "類型名稱重複！", Toast.LENGTH_SHORT).show();
	    }
	} catch (Exception e) {
	    Toast.makeText(father, "類型數據庫更新錯誤：" + e.toString(), Toast.LENGTH_LONG).show();
	    return false;
	} finally {
	    cursor.close();
	    sld.close();
	}
	return true;
    }

    public static void deleteType(Context father, String s) {
	try {
	    sld = SQLiteDatabase.openDatabase(DB_NAME, null, SQLiteDatabase.OPEN_READWRITE);
	    String sql = "delete from type where tname='" + s + "'";
	    sld.execSQL(sql);
	    Toast.makeText(father, "成功刪除類型", Toast.LENGTH_SHORT).show();
	} catch (Exception e) {
	    Toast.makeText(father, "類型刪除錯誤：" + e.toString(), Toast.LENGTH_LONG).show();
	} finally {
	    sld.close();
	}
    }

    public static ArrayList<String> getAllType(Context father)// 獲得所有日程的類型，包括軟件中用戶自建的，和軟件中已經被刪但是在數據庫中存儲的日程卻還在用的類型
    {
	ArrayList<String> type = new ArrayList<String>();
	type = alType;// 把軟件中存在的類型放入type
	try {
	    sld = SQLiteDatabase.openDatabase(DB_NAME, null, SQLiteDatabase.OPEN_READONLY);
	    String sql = "select distinct type from schedule;";// 因為存儲在數據庫中的日程的類型可能被用戶刪除，所以要搜索一遍
	    Cursor cursor = sld.rawQuery(sql, new String[] {});
	    while (cursor.moveToNext()) {
		if (!type.contains(cursor.getString(0))) {
		    type.add(cursor.getString(0));
		}
	    }
	    sld.close();
	    cursor.close();
	} catch (Exception e) {
	    Toast.makeText(father, "獲取類型錯誤：" + e.toString(), Toast.LENGTH_LONG).show();
	    Log.e("exception!!", e.toString());
	}
	return type;
    }

    // ============================所有處理類型數據庫的方法end==============================
    // ============================所有處理日程數據庫的方法start==============================
    public static void loadSchedule(DmActivity father)// 從日程數據庫讀取日程數據
    {
	try {
	    sld = SQLiteDatabase.openDatabase(DB_NAME, null, SQLiteDatabase.OPEN_READWRITE
		    | SQLiteDatabase.CREATE_IF_NECESSARY);
	    String sql = "create table if not exists schedule(" + "event_id integer primary key,"
		    + "title varchar2(40)," + "note varchar2(120)," + "type varchar2(20),"
		    + "timeset boolean," + "date1 char(10)," + "time1 char(5),"
		    + "alarmset boolean," + "time2 char(5)" + ")";
	    sld.execSQL(sql);
	    Cursor cursor = sld.query("schedule", null, null, null, null, null,
		    "date1 ASC,time1 ASC");// 按datetime1倒排序
	    while (cursor.moveToNext()) {
		int event_id = cursor.getInt(0);
		String title = cursor.getString(1);
		String note = cursor.getString(2);
		String type = cursor.getString(3);
		String timeSet = cursor.getString(4);
		String date1 = cursor.getString(5);
		String time1 = cursor.getString(6);
		String alarmSet = cursor.getString(7);
		String time2 = cursor.getString(8);
		Schedule schTemp = new Schedule(event_id, title, note, type, timeSet, date1, time1,
			alarmSet, time2);
		alSch.add(schTemp);
		Log.d("schdata", "[" + cursor.getPosition() + "]" + "event_id=" + event_id
			+ ",date=" + date1 + ",time=" + time1);
	    }
	    sld.close();
	    cursor.close();
	} catch (Exception e) {
	    Toast.makeText(father, "日程數據庫打開創建錯誤：" + e.toString(), Toast.LENGTH_LONG).show();
	    Log.e("日程數據庫打開創建錯誤!!", e.toString());
	}
    }

    public static void insertSchedule(DmActivity father)// 插入日程
    {
	try {
	    sld = SQLiteDatabase.openDatabase(DB_NAME, null, SQLiteDatabase.OPEN_READWRITE);
	    util.addEvent();
	    String sql = father.schTemp.toInsertSql();
	    Log.d("insertSQL", sql);
	    sld.execSQL(sql);
	    sld.close();
	} catch (Exception e) {
	    Toast.makeText(father, "日程數據庫新增錯誤：" + e.toString(), Toast.LENGTH_LONG).show();
	    Log.e("日程數據庫新增錯誤!!", e.toString());
	}
    }

    public static void insertSchedule2(DmActivity father, int event_id, String title, String note,
	    String type, String timeSet, String date1, String time1, String alarmSet, String time2)// 更新日程
    {
	try {
	    sld = SQLiteDatabase.openDatabase(DB_NAME, null, SQLiteDatabase.OPEN_READWRITE);
	    Schedule schTemp = new Schedule(event_id, title, note, type, timeSet, date1, time1,
		    alarmSet, time2);
	    String sql = schTemp.toInsertSql2();
	    Log.d("insertSql2", sql);
	    sld.execSQL(sql);
	    sld.close();
	} catch (Exception e) {
	    Toast.makeText(father, "日程數據庫新增錯誤：" + e.toString(), Toast.LENGTH_LONG).show();
	    Log.e("日曆to數據庫新增錯誤!!", e.toString());
	}
    }

    public static void updateSchedule(DmActivity father)// 更新日程
    {
	try {
	    sld = SQLiteDatabase.openDatabase(DB_NAME, null, SQLiteDatabase.OPEN_READWRITE);
	    util.editEvent();
	    String sql = father.schTemp.toUpdateSql();
	    Log.d("updateSQL", sql);
	    sld.execSQL(sql);
	    sld.close();
	} catch (Exception e) {
	    Toast.makeText(father, "日程數據庫更新錯誤：" + e.toString(), Toast.LENGTH_LONG).show();
	    Log.d("日程數據庫更新錯誤!!", e.toString());
	}
    }

    public static void updateSchedule2(DmActivity father, int event_id, String title, String note,
	    String type, String timeSet, String date1, String time1, String alarmSet, String time2)// 更新日程
    {
	try {
	    sld = SQLiteDatabase.openDatabase(DB_NAME, null, SQLiteDatabase.OPEN_READWRITE);
	    Schedule schTemp = new Schedule(event_id, title, note, type, timeSet, date1, time1,
		    alarmSet, time2);
	    String sql = schTemp.toUpdateSql2();
	    Log.d("updateSQL2", sql);
	    sld.execSQL(sql);
	    sld.close();
	} catch (Exception e) {
	    Toast.makeText(father, "日程數據庫更新錯誤：" + e.toString(), Toast.LENGTH_LONG).show();
	    Log.e("日曆to數據庫更新錯誤!!", e.toString());
	}
    }

    public static void deleteSchedule(DmActivity father, int id)// 刪除日程
    {
	try {
	    sld = SQLiteDatabase.openDatabase(DB_NAME, null, SQLiteDatabase.OPEN_READWRITE);
	    util.delEvent(id);
	    String sql = "delete from schedule where event_id=" + id;
	    Log.d("deleteSQL", sql);
	    sld.execSQL(sql);
	    sld.close();
	} catch (Exception e) {
	    Toast.makeText(father, "日程刪除錯誤：" + e.toString(), Toast.LENGTH_LONG).show();
	    Log.e("日程刪除錯誤!!", e.toString());
	}
    }

    public static void deleteSchedule2(DmActivity father, String eventid)// 同步時刪除日曆上不存在的日程
    {
	try {
	    sld = SQLiteDatabase.openDatabase(DB_NAME, null, SQLiteDatabase.OPEN_READWRITE);
	    String sql = "delete from schedule where event_id=" + eventid;
	    Log.d("deleteSQL2", sql);
	    sld.execSQL(sql);
	    sld.close();
	} catch (Exception e) {
	    Toast.makeText(father, "日程刪除錯誤：" + e.toString(), Toast.LENGTH_LONG).show();
	    Log.e("日曆to數據庫刪除錯誤!!", e.toString());
	}
    }

    public static void deletePassedSchedule(Context context)// 刪除所有過期日程
    {
	try {
	    sld = SQLiteDatabase.openDatabase(DB_NAME, null, SQLiteDatabase.OPEN_READWRITE);
	    String nowDate = getNowDateString();
	    String nowTime = getNowTimeString();
	    String sql = "delete from schedule where date1<'" + nowDate + "' or date1='" + nowDate
		    + "' and time1<'" + nowTime + "'";
	    Log.d("deletePassedSQL", sql);
	    sld.execSQL(sql);
	    sld.close();
	    Toast.makeText(context, "已將過期日程刪除", Toast.LENGTH_SHORT).show();
	} catch (Exception e) {
	    Toast.makeText(context, "日程刪除錯誤：" + e.toString(), Toast.LENGTH_LONG).show();
	    Log.e("刪除過期日程錯誤!!", e.toString());
	}
    }

    public static void searchSchedule(Context father, ArrayList<String> allKindsType)// 查找日程
    {
	ArrayList<Boolean> SelectedType = alSelectedType;
	try {
	    sld = SQLiteDatabase.openDatabase(DB_NAME, null, SQLiteDatabase.OPEN_READONLY);
	    String[] args = new String[2];
	    args[0] = rangeFrom;
	    args[1] = rangeTo;
	    String sql = "select * from schedule where date1 between ? and ?";
	    StringBuffer sbtmp = new StringBuffer();
	    sbtmp.append(" and (type=");
	    for (int i = 0; i < SelectedType.size(); i++) {
		if (SelectedType.get(i)) {
		    sbtmp.append("'");
		    sbtmp.append(allKindsType.get(i));
		    sbtmp.append("' or type=");
		}
	    }
	    String strSelectedType = sbtmp.toString();
	    strSelectedType = strSelectedType.substring(0, strSelectedType.length() - 9);// 最後去掉後面的" or type="
	    sql += strSelectedType + ")";
	    Log.d("searchSQL", sql);
	    Cursor cursor = sld.rawQuery(sql, args);
	    Toast.makeText(father, "搜索到" + cursor.getCount() + "條日程", Toast.LENGTH_SHORT).show();
	    alSch.clear();
	    while (cursor.moveToNext()) {
		int event_id = cursor.getInt(0);
		String title = cursor.getString(1);
		String note = cursor.getString(2);
		String type = cursor.getString(3);
		String timeSet = cursor.getString(4);
		String date1 = cursor.getString(5);
		String time1 = cursor.getString(6);
		String alarmSet = cursor.getString(7);
		String time2 = cursor.getString(8);
		Schedule schTemp = new Schedule(event_id, title, note, type, timeSet, date1, time1,
			alarmSet, time2);
		alSch.add(schTemp);
	    }
	    sld.close();
	    cursor.close();
	} catch (Exception e) {
	    Toast.makeText(father, e.toString(), Toast.LENGTH_SHORT).show();
	}
    }

    public static ArrayList<String> getAllEventId(Context father)// 獲得所有日程的EventId
    {
	ArrayList<String> eventid = new ArrayList<String>();
	eventid = alEventId;
	try {
	    sld = SQLiteDatabase.openDatabase(DB_NAME, null, SQLiteDatabase.OPEN_READONLY);
	    String sql = "select distinct event_id from schedule";
	    Cursor cursor = sld.rawQuery(sql, new String[] {});
	    while (cursor.moveToNext()) {
		if (!eventid.contains(cursor.getString(0))) {
		    eventid.add(String.valueOf(cursor.getInt(0)));
		    Log.d("getAllEventId", "[" + cursor.getPosition() + "]" + cursor.getInt(0));
		}
	    }
	    sld.close();
	    cursor.close();
	} catch (Exception e) {
	    Toast.makeText(father, "獲取事件ID錯誤：" + e.toString(), Toast.LENGTH_LONG).show();
	    Log.e("exception!!", e.toString());
	}
	return eventid;
    }

    public static String getType(int event_id)// 獲得所有日程的EventId
    {
	String type = null;
	try {
	    sld = SQLiteDatabase.openDatabase(DB_NAME, null, SQLiteDatabase.OPEN_READONLY);
	    String sql = "select distinct type from schedule where event_id=" + event_id;
	    Cursor cursor = sld.rawQuery(sql, new String[] {});
	    cursor.moveToFirst();
	    type = cursor.getString(0);
	    sld.close();
	    cursor.close();
	} catch (Exception e) {
	    Log.e("exception!!", e.toString());
	}
	return type;
    }
}
